The present invention relates to relational databases, and more specifically, to gap detection in a temporally unique index in a relational database. A relational database can be generally described as a collection of data items organized as a set of formally described tables from which data can be accessed easily. The software used in a relational database is typically referred to as a Relational Database Management System (RDBMS). One example of an RDBMS is the DB2 Universal Database (DB2 UDB), which is available from International Business Machines Corporation of Armonk, N.Y.
One type of tables that is often used in a RDBMS is Application-Maintained Temporal Tables. Temporal Tables contain data that is known to be valid for a given duration of time. For example, the interest rate of a loan to a customer has a certain value each month or year, but the rate may fluctuate during the lifespan of the loan. There are two types of constraints that database servers can handle on top of Application-Maintained Temporal Tables: Temporal Uniqueness and Gap Elimination.
Temporal Uniqueness constraints (also referred to as “WITHOUT OVERLAPS” constraints in the DB2 UDB system) ensures that each row of an Application-Maintained Temporal Table describes a discrete period of time, i.e. that a given customer's interest rate for a loan only has one value for any given instant of time during the life of the loan.
Gap Elimination constraints (herein also referred to as “WITHOUT GAPS”) ensure that there is no gap in the values over the duration of the user's data, i.e., that there does not exist an instance of time from the start of the loan until the end of the loan for which no interest rate is recorded.
In some RDBMSs, such as the DB2 UDB, users can create their own gap-elimination triggers or Structured Query Language (SQL) queries to detect gaps. However, these SQL queries are much more complex than temporal uniqueness in that gap elimination queries need to use Online Analytical Processing (OLAP) or other analytical functions to determine the ‘next’ and ‘previous’ row values in an index, i.e.,
SELECT * FROM(SELECT STOCK_ID, MAX(END_DATE) OVER (ORDER BYBEGIN_DATE) GAP_START,LEAD(BEGIN_DATE) OVER (ORDER BY BEGIN_DATE)GAP_END FROM T1)WHERE GAP_START < GAP_END
Additionally, users who use a SQL-based gap elimination scheme may get “false positives,” as the index is not locked after an Update/Delete/Insert (UDI) operation and before the SQL statement above is read, and it requires the users to tailor the queries to match the index they want to monitor for gaps.
Another alternative technique is to perform an index scan of the table after an UDI operation, searching all keys that match the non-temporal key parts that were modified, and ensuring that the end times of the current row equals the start time of the next row during the scan. However, this introduces issues with locking and concurrency, since the scan must take place after the index locks and page locks on the data have been released.